import sqlite3

sqlite_connect_handle = sqlite3.connect("diff_symbol.db")
sqlite_connect_handle_relevance = sqlite3.connect("cen7.0.db", timeout=30)

sqlite3_cursor_handle=sqlite_connect_handle.cursor()
sqlite3_cursor_handle_relevance=sqlite_connect_handle_relevance.cursor()


def insertValue2Table(value):
    try:
        sqlite3_cursor_handle.execute(value)
        sqlite_connect_handle.commit()
    except sqlite3.IntegrityError as e:
        print("sqlite UNIQUE faild")


def createTable():
    sqlite3_cursor_handle.execute("CREATE TABLE IF NOT EXISTS `centos_lib_rpmpackage_relevance` ( \
	`libname`	TEXT, \
	`rpmpackagename`	TEXT, \
	`is_link`	INTEGER, \
	`link_file`	TEXT, \
	`osversion`	TEXT ,\
    PRIMARY KEY(`libname`,`rpmpackagename`));")
    sqlite_connect_handle.commit()

    sqlite3_cursor_handle.execute("CREATE TABLE IF NOT EXISTS `CQ24_lib_rpmpackage_relevance` ( \
	`libname`	TEXT, \
	`rpmpackagename`	TEXT, \
	`is_link`	INTEGER, \
	`link_file`	TEXT, \
	`osversion`	TEXT, \
    PRIMARY KEY(`libname`,`rpmpackagename`));")
    sqlite_connect_handle.commit()

    sqlite3_cursor_handle.execute("CREATE TABLE  IF NOT EXISTS `lib_symbol_diff` ( \
	`cen_libname`	TEXT, \
	`cq_libname`	TEXT, \
	`symbol_name`	TEXT, \
	`effect_type`	TEXT, \
	`effect_result`	TEXT, \
    `effect_file`	TEXT,\
    PRIMARY KEY(`cen_libname`,`symbol_name`));")
    sqlite_connect_handle.commit()


def insertLibRpmPakcageRelevanceValue2Table(libname,rpmpackgename,is_link,link_file,osverion):
    sql="insert into lib_rpmpackage_relevance values('{}','{}',{},'{}','{}')".format(libname,rpmpackgename,is_link,link_file,osverion)
    print(sql)
    insertValue2Table(sql)

def insertLibSymbolDiffValue2Table(cenlibname,cqlibname,symboldiff,effecttype,effectresult,effect_file,verdict):
    sql="insert into lib_symbol_diff values('{}','{}','{}','{}',\"{}\",'{}',{})".format(cenlibname,cqlibname,symboldiff,effecttype,effectresult,effect_file,verdict)
    print(sql)
    insertValue2Table(sql)

def selectCenLibnameBylibRPMrelevance(cenlibname):
  sql="select libname from centos_lib_rpmpackage_relevance where libname like '%{}'".format(cenlibname)
  print(sql)
  sqlite3_cursor_handle_relevance.execute(sql)
  result=sqlite3_cursor_handle_relevance.fetchall()
  if result:
    return str(result[0][0])
  else:
    return None

def selectCqLibnameBylibRPMrelevance(cqlibname):
  sql="select libname from CQ24_lib_rpmpackage_relevance where libname like '%{}'".format(cqlibname)
  print(sql)
  sqlite3_cursor_handle_relevance.execute(sql)
  result=sqlite3_cursor_handle_relevance.fetchall()
  if result:
    return str(result[0][0])
  else:
    return None